import pandas as pd

# Read CSV file
# Define the widths of each column based on fixed-width format
p1301_width = [26] * 48

df = pd.read_fwf('Z_CAWN_I_58448_20221016110000_O_GHG-FLD-CO2CH4-CRDS-S024', widths=p1301_width)

# Preprocess column names
sanitized_columns = [col.replace('(', '').replace(')', '') for col in df.columns]
sanitized_columns = ['TOTAL_FILTERED_CH4' if col =='TOTAL_FILTERED_CH4 FITQUAL' else col for col in sanitized_columns]
sanitized_columns = ['FITQUALITY_H2O' if col =='ITY_H2O' else col for col in sanitized_columns]
#sanitized_columns = ['otime' if col == 'time' else col for col in sanitized_columns]

# Rename columns
df.columns = sanitized_columns

print(df.columns)
data_types = {col: 'CHAR(10)' if i < 2 else 'DOUBLE' for i, col in enumerate(df.columns)}

# Create MySQL table
# Assuming you have a MySQL connection established using a library like pymysql
# Adjust the following code to fit your database setup
import pymysql

conn = pymysql.connect(host='localhost', user='grafana', password='grafana', database='grafanadb')
cursor = conn.cursor()

# Construct CREATE TABLE query
create_table_query = f"CREATE TABLE rdt_1301 ({', '.join([f'{col} {data_types[col]}' for col in df.columns])})"

# Execute query
cursor.execute(create_table_query)

# Insert data into MySQL table
# Assuming your_table already exists in your MySQL database
df.to_sql('rdt_1301', conn, if_exists='append', index=False)

# Commit changes and close connection
conn.commit()
conn.close()
